import datetime
import pandas as pd
import numpy as np
import pymysql
from time import *
import os
# 展示所有的列
pd.set_option('display.max_columns', None)

# 将顾客信息明细表转为dataframe
def get_client_detail_df(client_table_name):
    db = pymysql.connect(host="localhost", user="root", password="zcxvbmn", database="weixindb")
    cursor = db.cursor()
    sql = "select * from %s" % client_table_name
    cursor.execute(sql)
    client_detail = cursor.fetchall()
    client_detail_df = pd.DataFrame(list(client_detail),
                                    columns=['id', 'userid', 'createtime', 'add_way', 'external_userid', 'type', 'gender'])
    db.close()
    return client_detail_df

# 从数据库拉取客户明细
client_detail_df = get_client_detail_df("client_detail_list")

# 计算顾客最早的添加时间
min_createtime = client_detail_df['createtime'].groupby(client_detail_df['external_userid']).min()

# 将最早添加时间与顾客明细表合并
client_detail_new_df = pd.merge(client_detail_df, min_createtime, left_on='external_userid', right_index=True)

# 为最早添加的顾客打标
client_detail_new_df['mark'] = client_detail_new_df.apply(lambda x: 1 if x['createtime_x'] == x['createtime_y'] else 0, axis=1)
print(client_detail_new_df)

# 计算各员工顾客总数与去重顾客数、企业顾客与个人顾客，添加方式
member_client_onlycount = client_detail_new_df.pivot_table(index='userid', columns='mark', aggfunc='count', fill_value=0, margins=True)['createtime_x']
member_client_type_count = client_detail_new_df.pivot_table(index='userid', columns='type', aggfunc='count', fill_value=0)['createtime_x']
member_client_addway_count = client_detail_new_df.pivot_table(index='userid', columns='add_way', aggfunc='count', fill_value=0)['createtime_x']
member_client_gender_count = client_detail_new_df.pivot_table(index='userid', columns='gender', aggfunc='count', fill_value=0)['createtime_x']
member_count = pd.merge(pd.merge(pd.merge(member_client_onlycount, member_client_type_count, left_index=True, right_index=True),member_client_addway_count, left_index=True, right_index=True), member_client_gender_count, left_index=True, right_index=True)
print(member_count)

with pd.ExcelWriter('D:\member_count%s.xlsx' % (strftime('%Y%m%d%H%M%S', localtime(time())))) as writer:
    member_count.to_excel(writer, sheet_name='到人明细')